package com.example.librarymanagementsystembackend.utils;

import java.io.*;
import java.nio.charset.StandardCharsets;
import java.sql.*;
import java.util.Arrays;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class SQLFileExecutor {

    private final String dbUrl;
    private final String dbUser;
    private final String dbPassword;

    // 预期字段列表（注意顺序）
    private static final String[] EXPECTED_FIELDS = {
            "id", "isbn", "title", "author", "coverUrl", "stock", "categoryId", "createdAt", "updatedAt"
    };

    public SQLFileExecutor(String dbUrl, String dbUser, String dbPassword) {
        this.dbUrl = dbUrl;
        this.dbUser = dbUser;
        this.dbPassword = dbPassword;
    }

    /**
     * 主方法：校验字段并执行 SQL 文件
     */
    public boolean validateFieldsAndExecute(InputStream inputStream) throws IOException, SQLException {
        // 将输入流缓存为字节数组，以便多次读取
        ByteArrayOutputStream buffer = new ByteArrayOutputStream();
        int data;
        while ((data = inputStream.read()) != -1) {
            buffer.write(data);
        }
        byte[] fileBytes = buffer.toByteArray();

        String fileContent = new String(fileBytes, StandardCharsets.UTF_8);
        System.out.println(fileContent);

        Pattern patternWithFields = Pattern.compile(
                "DROP\\s+TABLE\\s+IF\\s+EXISTS\\s+`books`;\\s+CREATE\\s+TABLE\\s+`books`\\s*\\(\\s*`id`\\s+int\\(11\\)\\s+NOT\\s+NULL\\s+AUTO_INCREMENT,\\s+`isbn`\\s+varchar\\(20\\)\\s+CHARACTER\\s+SET\\s+utf8mb4\\s+COLLATE\\s+utf8mb4_unicode_ci\\s+NOT\\s+NULL,\\s+`title`\\s+varchar\\(255\\)\\s+CHARACTER\\s+SET\\s+utf8mb4\\s+COLLATE\\s+utf8mb4_unicode_ci\\s+NOT\\s+NULL,\\s+`author`\\s+varchar\\(100\\)\\s+CHARACTER\\s+SET\\s+utf8mb4\\s+COLLATE\\s+utf8mb4_unicode_ci\\s+NOT\\s+NULL,\\s+`coverUrl`\\s+varchar\\(255\\)\\s+CHARACTER\\s+SET\\s+utf8mb4\\s+COLLATE\\s+utf8mb4_unicode_ci\\s+DEFAULT\\s+NULL,\\s+`stock`\\s+int\\(11\\)\\s+NOT\\s+NULL\\s+DEFAULT\\s+0,\\s+`categoryId`\\s+int\\(11\\)\\s+DEFAULT\\s+NULL,\\s+`createdAt`\\s+timestamp\\(0\\)\\s+NOT\\s+NULL\\s+DEFAULT\\s+CURRENT_TIMESTAMP,\\s+`updatedAt`\\s+timestamp\\(0\\)\\s+NOT\\s+NULL\\s+DEFAULT\\s+CURRENT_TIMESTAMP\\s+ON\\s+UPDATE\\s+CURRENT_TIMESTAMP\\(0\\),\\s+PRIMARY\\s+KEY\\s+\\(`id`\\)\\s+USING\\s+BTREE,\\s+UNIQUE\\s+INDEX\\s+`isbn`\\(`isbn`\\)\\s+USING\\s+BTREE,\\s+INDEX\\s+`idx_books_category`\\(`categoryId`\\)\\s+USING\\s+BTREE,\\s+CONSTRAINT\\s+`books_ibfk_1`\\s+FOREIGN\\s+KEY\\s+\\(`categoryId`\\)\\s+REFERENCES\\s+`bookcategories`\\s+\\(`id`\\)\\s+ON\\s+DELETE\\s+SET\\s+NULL\\s+ON\\s+UPDATE\\s+RESTRICT\\s*\\)\\s+ENGINE\\s*=\\s*InnoDB\\s+AUTO_INCREMENT\\s*=\\s*30\\s+CHARACTER\\s+SET\\s*=\\s*utf8mb4\\s+COLLATE\\s*=\\s*utf8mb4_unicode_ci\\s+ROW_FORMAT\\s*=\\s*Dynamic;",
                Pattern.CASE_INSENSITIVE | Pattern.DOTALL | Pattern.MULTILINE
        );
        Matcher matcherWithFields = patternWithFields.matcher(fileContent);
        boolean fieldVerified = matcherWithFields.find();
        System.out.println("无关紧要！");
        if (!fieldVerified) {
            throw new InvalidClassException("SQL文件不是本系统的备份文件");
        }
        try {
            executeSQLFile(new ByteArrayInputStream(fileBytes));
        } catch(Exception e) {
            e.printStackTrace();
            throw new InvalidClassException("SQL文件存在错误");
        }
        return true;
//        System.out.println("原始 SQL 内容:");
//        System.out.println(fileContent);
//
//        boolean fieldVerified = false;
//
//        // 支持两种格式：
//        // 1. INSERT INTO books (fields...) VALUES ...
//        // 2. INSERT INTO books VALUES ...
//
//        Pattern patternWithFields = Pattern.compile(
//                "insert\\s+(ignore)?\\s+into\\s+`?books`?\\s*(?:\\$\\s*([^)]+?)\\s*\\$)?",
//                Pattern.CASE_INSENSITIVE | Pattern.DOTALL | Pattern.MULTILINE
//        );
//        Matcher matcherWithFields = patternWithFields.matcher(fileContent);
//
//        if (matcherWithFields.find()) {
//            String fieldList = matcherWithFields.group(2); // group(2) 是括号内的字段列表
//
//            if (fieldList == null || fieldList.trim().isEmpty()) {
//                System.out.println("⚠️ 使用了无字段列表的插入语法：INSERT INTO books VALUES (...)");
//                fieldVerified = true; // 允许导入（你也可以在这里加逻辑判断是否允许这种格式）
//            } else {
//                fieldList = fieldList.replaceAll("`", "").replaceAll("\\s+", "");
//
//                String[] actualFields = fieldList.split(",");
//                for (int i = 0; i < actualFields.length; i++) {
//                    actualFields[i] = actualFields[i].trim();
//                }
//
//                System.out.println("实际字段: " + Arrays.toString(actualFields));
//                System.out.println("预期字段: " + Arrays.toString(EXPECTED_FIELDS));
//
//                boolean match = true;
//                for (int i = 0; i < EXPECTED_FIELDS.length; i++) {
//                    if (i >= actualFields.length || !EXPECTED_FIELDS[i].equalsIgnoreCase(actualFields[i])) {
//                        match = false;
//                        break;
//                    }
//                }
//
//                if (match) {
//                    System.out.println("✅ 字段校验通过！");
//                    fieldVerified = true;
//                } else {
//                    throw new IllegalArgumentException("字段名不一致，请检查字段顺序");
//                }
//            }
//        } else {
//            throw new IllegalArgumentException("未找到符合格式的插入语句");
//        }
//
//        // 第二次读取：执行 SQL 文件
//        if (fieldVerified) {
//            executeSQLFile(new ByteArrayInputStream(fileBytes)); // 使用缓存的字节流
//            return true;
//        } else {
//            throw new IllegalArgumentException("未找到符合格式的插入语句");
//        }
    }

    /**
     * 执行 SQL 文件
     */
    public void executeSQLFile(InputStream inputStream) throws IOException, SQLException {
        try (Connection conn = DriverManager.getConnection(dbUrl, dbUser, dbPassword);
             Statement stmt = conn.createStatement()) {

            BufferedReader reader = new BufferedReader(new InputStreamReader(inputStream));
            StringBuilder sqlCommand = new StringBuilder();
            String line;

            while ((line = reader.readLine()) != null) {
                line = line.trim();
                if (line.isEmpty() || line.startsWith("--")) continue;

                sqlCommand.append(line);

                if (line.endsWith(";")) {
                    String sql = sqlCommand.toString();
                    sqlCommand.setLength(0);

                    stmt.execute(sql);
                }
            }
        }
    }
}
